<?php  
/*学生，模型*/
class Student_model extends CI_Model
{

	var $table = 'student';

	function __construct()
	{
		parent::__construct();

	}

	/**
	 * 返回数据表总条目个数
	 * @return [type] [description]
	 */
	function count_all()
	{
		return $this->db->count_all($this->table);
	}
	/**
	 * 得到某个学校的学生的数量
	 * @return [type] [description]
	 */
	function count_by_school_id($school_id)
	{
		$sql = 'SELECT count(*) as count FROM student WHERE school_id=?';
		$res = $this->db->query($sql, $school_id)->row_array();
		return $res['count'];
	}
	/**
	 * 获得所有的条目
	 * @return [type] [description]
	 */
	function get_all()
	{
		$res = $this->db->get($this->table)->result_array();
		return $res;
	}

	/**
	 * 通过id返回条目
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	function get_by_id($id)
	{
		return $this->db->get_where($this->table, array('id' => $id))->row_array();
	}
	/**
	 * 通过school_id返回条目
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	function get_by_school_id($school_id)
	{
		return $this->db->get_where($this->table, array('school_id' => $school_id))->result_array();
	}

	/**
	 * 添加一个条目
	 * @param [type] $item [description]
	 */
	function add($item)
	{
		return $this->db->insert($this->table, $item);
	}

	/**
	 * 删除一个条目
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	function delete($id)
	{
		return $this->db->delete($this->table, array('id' => $id));

	}

	/**
	 * 更新一个条目
	 * @param  [type] $id   [description]
	 * @param  [type] $item [description]
	 * @return [type]       [description]
	 */
	function update($id, $item)
	{
		$this->db->where('id', $id);
		return $this->db->update($this->table, $item);
	}

	/**
	 * 得到所有学生信息，包括专业，班级
	 * @return [type] [description]
	 */
	function get_all_students()
	{
		$sql = 'SELECT s.id,s.user_id,s.name,s.student_id,m.name as major_name,s.type,c.name as class_name FROM student as s,major as m,class as c WHERE s.major_id=m.id and s.class_id=c.id';
		return $this->db->query($sql)->result_array();
	}

	function get_no_course_students()
	{
		$sql = 'SELECT s.student_id as student_id,s.name as student_name,l.name as logic_class_name FROM student as s,student_in_logic_class as ss,logic_class as l WHERE l.id = ss.id and s.id not in (SELECT student_id FROM student_in_logic_class)';
		return $this->db->query($sql)->result_array();
	}

	function get_no_course_students_by_school_id($school_id)
	{
		$sql = 'SELECT s.student_id as student_id,s.name as student_name,l.name as logic_class_name FROM student as s,student_in_logic_class as ss,logic_class as l WHERE l.id = ss.id and s.id not in (SELECT student_id FROM student_in_logic_class) and l.school_id = ?';
		return $this->db->query($sql, $school_id)->result_array();
	}

	function get_students_by_course($id)
	{
		$sql = 'SELECT s.student_id as student_id,s.name as student_name,l.name as logic_class_name FROM student as s,student_in_logic_class as ss,logic_class as l WHERE l.id = ss.id and l.id = ?';
		return $this->db->query($sql, $id)->result_array();
	}


	/**
	 * 查询学生
	 * @param  [type] $major_id       [description]
	 * @param  [type] $class_id       [description]
	 * @param  [type] $logic_class_id [description]
	 * @param  [type] $student_type   [description]
	 * @return [type]                 [description]
	 */
	function search($major_id, $class_id, $logic_class_id, $student_type)
	{
		if ($logic_class_id) {
			$sql = 'SELECT s.id,s.user_id,s.name,s.student_id,m.name as major_name,s.type,c.name as class_name FROM student as s,major as m,class as c,student_in_logic_class as silc WHERE s.major_id=m.id and s.class_id=c.id and silc.student_id=s.id and silc.logic_class_id=?';
			if ($major_id)
				$sql = $sql . ' and s.major_id=' . $major_id;
			if ($class_id)
				$sql = $sql . ' and s.class_id=' . $class_id;
			if ($student_type)
				$sql = $sql . ' and s.type=' . $student_type;

			return $this->db->query($sql, $logic_class_id)->result_array();
		} else {
			$sql = 'SELECT s.id,s.user_id,s.name,s.student_id,m.name as major_name,s.type,c.name as class_name FROM student as s,major as m,class as c WHERE s.major_id=m.id and s.class_id=c.id';
			if ($major_id)
				$sql = $sql . ' and s.major_id=' . $major_id;
			if ($class_id)
				$sql = $sql . ' and s.class_id=' . $class_id;
			if ($student_type)
				$sql = $sql . ' and s.type=' . $student_type;
			return $this->db->query($sql)->result_array();
		}
	}

	/**
	 * 删除学生选课信息
	 * @param [type] $student_id    [description]
	 * @param [type] $logic_classes [description]
	 */
	function delete_relative_logic_classes($id)
	{
		$sql = 'delete from student_in_logic_class where student_id = ?';
		return $this->db->query($sql, $id);
	}

	/**
	 * 添加学生选课信息
	 * @param [type] $student_id    [description]
	 * @param [type] $logic_classes [description]
	 */
	function add_relative_logic_classes($student_id, $logic_classes)
	{
		foreach ($logic_classes as $logic_class_id) {
			$sql = 'SELECT count(*) as count FROM student_in_logic_class WHERE student_id=? and logic_class_id=?';
			$row = $this->db->query($sql, array($student_id, $logic_class_id))->row_array();
			if ($row['count'] > 0)
				continue;
			$relation = array(
				'logic_class_id' => $logic_class_id,
				'student_id' => $student_id
			);
			if (!$this->db->insert('student_in_logic_class', $relation))
				return false;
		}
		return true;
	}

	/**
	 * 添加校外学生选课信息
	 * @param [type] $student_id    [description]
	 * @param [type] $logic_classes [description]
	 */
	function add_student_in_logic_classes($student_id, $logic_classes)
	{
		foreach ($logic_classes as $logic_class) {
			$sql = 'SELECT count(*) as count FROM student_in_logic_class WHERE student_id=? and logic_class_id=?';
			$row = $this->db->query($sql, array($student_id, $logic_class['id']))->row_array();
			if ($row['count'] > 0)
				continue;
			$relation = array(
				'logic_class_id' => $logic_class['id'],
				'student_id' => $student_id
			);
			if (!$this->db->insert('student_in_logic_class', $relation))
				return false;
		}
		return true;
	}
	//学生选择一门课
	function add_student_in_logic_class($student_in_logic_class)
	{
		return $this->db->insert('student_in_logic_class', $student_in_logic_class);
	}
	//查找某个学生是否已经加入了某个逻辑班
	function get_student_in_logic_class($student_id, $logic_class_id)
	{
		$sql = 'SELECT * FROM student_in_logic_class WHERE student_id=? and logic_class_id=?';
		return $this->db->query($sql, array($student_id, $logic_class_id))->row_array();
	}
	/**
	 * 得到学生选课信息
	 * @param [type] $student_id    [description]
	 * @param [type] $logic_classes [description]
	 */
	function get_logic_classes_select($id)
	{
		$sql = 'SELECT * FROM student_in_logic_class WHERE student_id=?';
		return $this->db->query($sql, $id)->result_array();
	}

	/**
	 * 得到学生的信息
	 * @param  [type] $id [description]
	 * @return [type]             [description]
	 */
	function get_student_by_id($id)
	{
		$sql = 'SELECT s.id,s.user_id,s.name,s.student_id,m.id as major_id,m.name as major_name,s.type FROM student as s,major as m WHERE s.major_id=m.id and s.id=?';
		return $this->db->query($sql, $id)->row_array();
	}

	/**
	 * 得到学生的选课信息
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	function get_relative_logic_classes($id)
	{
		$sql = 'SELECT lc.id,lc.name,bl.name as big_lecture_name,t.name as teacher_name,silc.grades as grades FROM big_lecture as bl,logic_class as lc,student_in_logic_class as silc,teacher as t WHERE lc.big_lecture_id=bl.id and t.id=lc.teacher_id and lc.id=silc.logic_class_id and silc.student_id=?';
		return $this->db->query($sql, $id)->result_array();
	}

	/**
	 * 删除一个学生(使用的是数据库id)
	 * @param  [type] $id [description]
	 * @return [type]             [description]
	 */
	function delete_cascade($student_id)
	{
		$student = $this->get_by_id($student_id);
		//删除学生所选课程
		$sql = 'DELETE FROM student_in_logic_class where student_id=?';
		if (!$this->db->query($sql, $student_id))
			return false;
		//删除学生的考试信息
		$sql = 'DELETE FROM question_in_report WHERE report_id in (SELECT * FROM (SELECT r.id FROM report as r,question_in_report as qir WHERE r.id=qir.report_id and r.student_id=?) as t)';
		if (!$this->db->query($sql, $student_id))
			return false;

		$sql = 'DELETE FROM report where student_id=?';
		if (!$this->db->query($sql, $student_id))
			return false;
		//删除这个学生
		if (!$this->delete($student_id))
			return false;
		//删除这个user
		$this->load->model('user_model');
		if (!$this->user_model->delete($student['user_id']))
			return false;

		return true;
	}

	/**
	 * 从一个逻辑班下删除一个学生
	 * @param  [type] $logic_class_id [description]
	 * @param  [type] $student_id     [description]
	 * @return [type]                 [description]
	 */
	function delete_from_logic_class($logic_class_id, $student_id)
	{
		$student = $this->get_by_id($student_id);
		//删除学生所选课程
		$sql = 'DELETE FROM student_in_logic_class WHERE logic_class_id=? AND student_id=?';
		if (!$this->db->query($sql, array($logic_class_id, $student_id)))
			return false;
		//删除学生的考试信息
		$sql = 'DELETE FROM question_in_report WHERE report_id in (SELECT * FROM (SELECT r.id FROM report as r,question_in_report as qir WHERE r.id=qir.report_id and r.logic_class_id=? and r.student_id=?) as t)';
		if (!$this->db->query($sql, array($logic_class_id, $student_id)))
			return false;
		$sql = 'DELETE FROM report WHERE logic_class_id=? AND student_id=?';
		if (!$this->db->query($sql, array($logic_class_id, $student_id)))
			return false;
		return true;
	}
	/**
	 * 学生退课
	 * @param  [type] $logic_class_id [description]
	 * @param  [type] $student_id     [description]
	 * @return [type]                 [description]
	 */
	function student_delete_from_logic_class($logic_class_id, $student_id)
	{
		$student = $this->get_by_id($student_id);
		//有考试信息禁止删除
		$sql = 'SELECT * FROM report WHERE logic_class_id=? AND student_id=?';
		if ($this->db->query($sql, array($logic_class_id, $student_id))->result_array())
			return false;

		$sql = 'SELECT * FROM question_in_report WHERE report_id in (SELECT * FROM (SELECT r.id FROM report as r,question_in_report as qir WHERE r.id=qir.report_id and r.logic_class_id=? and r.student_id=?) as t)';
		if ($this->db->query($sql, array($logic_class_id, $student_id))->result_array())
			return false;		
		//删除学生所选课程
		$sql = 'DELETE FROM student_in_logic_class WHERE logic_class_id=? AND student_id=?';
		if (!$this->db->query($sql, array($logic_class_id, $student_id)))
			return false;
		return true;
	}

	/**
	 * 通过学号得到学生
	 * @param  [type] $student_id [description]
	 * @return [type]             [description]
	 */
	function get_by_student_id($student_id)
	{
		$sql = 'SELECT * FROM student WHERE student_id=?';
		return $this->db->query($sql, $student_id)->row_array();
	}

	/**
	 * 添加一条选课记录
	 * @param [type] $student_id     [description]
	 * @param [type] $logic_class_id [description]
	 */
	function add_logic_class($student_id, $logic_class_id)
	{
		//检测是否已经存在这个选课记录
		$sql = 'SELECT count(*) as count FROM student_in_logic_class WHERE student_id=? and logic_class_id=?';
		$row = $this->db->query($sql, array($student_id, $logic_class_id))->row_array();
		if ($row['count'] > 0)
			return true;
		$relation = array(
			'logic_class_id' => $logic_class_id,
			'student_id' => $student_id
		);
		return $this->db->insert('student_in_logic_class', $relation);
	}

	/**
	 * 返回一个老师的所有学生
	 * @param  [type] $teacher_id [description]
	 * @return [type]             [description]
	 */
	function get_all_students_by_teacher_id($teacher_id)
	{
		$sql = 'SELECT DISTINCT s.id,s.user_id,s.name,s.student_id,m.name as major_name,s.type,c.name as class_name FROM student as s,major as m,class as c,student_in_logic_class as silc,logic_class as lc WHERE s.major_id=m.id and s.class_id=c.id and s.id=silc.student_id and lc.id=silc.logic_class_id and lc.teacher_id=?';
		return $this->db->query($sql, $teacher_id)->result_array();

	}

	/**
	 * 得到一个逻辑班下，一个班级下的学生
	 * @param  [type] $logic_class_id [description]
	 * @param  [type] $class_id       [description]
	 * @return [type]                 [description]
	 */
	function get_students_by_logic_class_id($logic_class_id)
	{
		$sql = 'SELECT s.id,s.user_id,s.name,s.student_id,m.name as major_name FROM major as m,student as s,student_in_logic_class as silc where m.id=s.major_id and s.id = silc.student_id and silc.logic_class_id=? ';
		return $this->db->query($sql, array($logic_class_id))->result_array();
	}




	/**
	 * 检查一个老师能不能对一个同学进行编辑
	 * @param  [type] 学生的id [description]
	 * @param  [type] $teacher_id [description]
	 * @return [type]             [description]
	 */
	function check_student_editable($student_id, $teacher_id)
	{
		$sql = 'SELECT count(*) as count FROM student_in_logic_class as silc,logic_class as lc WHERE silc.logic_class_id=lc.id and silc.student_id=? and lc.teacher_id<>?';
		$result = $this->db->query($sql, array($student_id, $teacher_id))->row_array();
		return ($result['count'] == 0);
	}

	/**
	 * 检查这个学生是否有选择的课程
	 * @param  [type] $student_id [description]
	 * @return [type]             [description]
	 */
	function check_in_logic_class($student_id)
	{
		$sql = 'SELECT count(*) as count FROM student_in_logic_class WHERE student_id=?';
		$result = $this->db->query($sql, $student_id)->row_array();
		return ($result['count'] > 0);
	}

	/**
	 * 查询一个老师的学生的表现
	 * @param  [type] $search_content [description]
	 * @return [type]                 [description]
	 */


	function get_performances($teacher_id, $search_content)
    //function get_performances($teacher_id)
	{
		//这样拼接字符串实际上不太安全
		$sql = 'SELECT s.name as student_name,s.student_id as student_id,lc.name as logic_class_name,bl.name as big_lecture_name,mo.name as module_name,r.id as report_id,r.grades as grades FROM report as r,module as mo,student as s,student_in_logic_class as silc,logic_class as lc,big_lecture as bl  where r.module_id=mo.id and r.student_id=s.id  and s.id=silc.student_id and lc.id=silc.logic_class_id and lc.big_lecture_id=bl.id and lc.teacher_id=? and mo.id not in (select m.parent_id from module as m where m.teacher_id =? and m.type = 1)';

		// $sql='SELECT s.name as student_name,s.student_id as student_id,lc.name as logic_class_name,bl.name as big_lecture_name,mo.name as module_name,r.id as report_id,r.grades as grades FROM report as r,module as mo,student as s,student_in_logic_class as silc,logic_class as lc,big_lecture as bl  where r.module_id=mo.id and r.student_id=s.id  and s.id=silc.student_id and lc.id=silc.logic_class_id and lc.big_lecture_id=bl.id and lc.teacher_id=?';

		if (isset($search_content['logic_class_id'])) {
			if (!$search_content['logic_class_id']) {
				//如果不按逻辑班查，就返回这个老师名下所有的逻辑班
				$sql = $sql . ' and lc.id in (select lc.id from logic_class as lc where lc.teacher_id=1)';
			} else {
				$sql = $sql . ' and lc.id=' . $search_content['logic_class_id'];
			}
		}
		if (isset($search_content['module_id']) && $search_content['module_id'])
			$sql = $sql . ' and mo.id=' . $search_content['module_id'];
		if (isset($search_content['student_name']) && $search_content['student_name'])
			$sql = $sql . ' and s.name like \'%' . $search_content['student_name'] . '%\'';
		if (isset($search_content['student_id']) && $search_content['student_id'])
			$sql = $sql . ' and s.student_id like \'%' . $search_content['student_id'] . '%\'';
		if (isset($search_content['grades_low']) && $search_content['grades_low'])
			$sql = $sql . ' and r.grades>=' . $search_content['grades_low'];
		if (isset($search_content['grades_high']) && $search_content['grades_high'])
			$sql = $sql . ' and r.grades<=' . $search_content['grades_high'];
		return $this->db->query($sql, array($teacher_id, $teacher_id))->result_array();
	}
	function get_performances_query($teacher_id, $search_content)
	{
		//这样拼接字符串实际上不太安全
		$sql = 'SELECT s.name as 姓名,s.student_id as 学号,lc.name as 班级,bl.name as 课程,mo.name as 模块,r.grades as 分数 FROM report as r,module as mo,student as s,student_in_logic_class as silc,logic_class as lc,big_lecture as bl  where r.module_id=mo.id and r.student_id=s.id and s.id=silc.student_id and lc.id=silc.logic_class_id and lc.big_lecture_id=bl.id and lc.teacher_id=?';
		if (isset($search_content['logic_class_id'])) {
			if (!$search_content['logic_class_id']) {
				//如果不按逻辑班查，就返回这个老师名下所有的逻辑班
				$sql = $sql . ' and lc.id in (select lc.id from logic_class as lc where lc.teacher_id=1)';
			} else {
				$sql = $sql . ' and lc.id=' . $search_content['logic_class_id'];
			}
		}
		if (isset($search_content['module_id']) && $search_content['module_id'])
			$sql = $sql . ' and mo.id=' . $search_content['module_id'];
		if (isset($search_content['student_name']) && $search_content['student_name'])
			$sql = $sql . ' and s.name like \'%' . $search_content['student_name'] . '%\'';
		if (isset($search_content['student_id']) && $search_content['student_id'])
			$sql = $sql . ' and s.student_id like \'%' . $search_content['student_id'] . '%\'';
		if (isset($search_content['grades_low']) && $search_content['grades_low'])
			$sql = $sql . ' and r.grades>=' . $search_content['grades_low'];
		if (isset($search_content['grades_high']) && $search_content['grades_high'])
			$sql = $sql . ' and r.grades<=' . $search_content['grades_high'];
		// return $this->db->query($sql,$teacher_id,$teacher_id);
		return $this->db->query($sql, $teacher_id);
	}

	/**
	 * 通过username返回一个student
	 * @param  [type] $username [description]
	 * @return [type]          [description]
	 */
	function get_by_username($username)
	{
		$sql = 'SELECT s.id,s.user_id,s.name,s.student_id,s.major_id,s.type FROM student as s,user as u WHERE s.user_id=u.id and u.username=?';
		return $this->db->query($sql, $username)->row_array();
	}

	/**
	 * 通过逻辑班号得到学生信息
	 * @return [type] [description]
	 */
	function get_students_from_logic_class_id($logic_class_id)
	{
		$sql = 'SELECT s.id,s.name,s.student_id,m.name as major_name,bl.name as big_lecture_name ,lc.name as logic_class_name FROM student_in_logic_class as silc,logic_class as lc,student as s,major as m,big_lecture as bl WHERE bl.id=lc.big_lecture_id and silc.student_id=s.id and silc.logic_class_id=lc.id and m.id=s.major_id and lc.id=?';
		return $this->db->query($sql, $logic_class_id)->result_array();
	}


	/**
	 * 通过user_id返回学生信息
	 * @param  [type] $user_id [description]
	 * @return [type]     	   [description]
	 */
	function get_by_user_id($user_id)
	{
		return $this->db->get_where($this->table, array('user_id' => $user_id))->row_array();
	}

	/**
	 * 通过学生ID返回学生的所参与的logic_class信息
	 * @param  [type] $student_id [description]
	 * @return [type]     		  [description]
	 */
	function get_student_logic_class($student_id)
	{
		return $this->db->get_where('student_in_logic_class', array('student_id' => $student_id))->result_array();
	}
	/**
	 * 查看数据库中这个学校是否已经有这个工号
	 * @param  [type] $teacher_number [description]
	 * @return [type]                 [description]
	 */
	function student_id_exist($student_id, $school_id)
	{
		$sql = 'SELECT count(*) as count FROM student WHERE student.student_id =? and student.school_id=?';
		$row_student = $this->db->query($sql, array($student_id, $school_id))->row_array();
		return ($row_student['count'] > 0);
	}
}
